<?php

namespace App\Services\Platform;

use App\Core\Traits\Numeral;
use App\Models\Game\Platform\Org;
use App\Models\Game\Settlement\Info;
use App\Services\Service;
use App\Services\Traits\DatabaseMysql;
use App\Models\Admin\Stats\Platform\Settlement\Day as StatsDay;
use App\Models\Admin\Stats\Platform\Settlement\Month as StatsMonth;
use Helper\Timed;
use Illuminate\Support\Facades\DB;
use Ramsey\Uuid\Type\Time;

class SettlementService extends Service
{
    use DatabaseMysql, Numeral;

	/**
	 * 获取数据列表【天】
	 *
	 * @param \Illuminate\Http\Request $request
	 * @param bool $export
	 * @return \Illuminate\Database\Eloquent\Collection[]|int[]|array[]
	 */
	public function day($request, $export = false)
	{
		return $this->getList($request, 'day', $export);
	}

	/**
	 * 获取数据列表【月】
	 *
	 * @param \Illuminate\Http\Request $request
	 * @param bool $export
	 * @return \Illuminate\Database\Eloquent\Collection[]|int[]|array[]
	 */
	public function month($request, $export = false)
	{
		$datetime = $request->input('datetime');

		if($datetime){
			date_default_timezone_set(Timed::getJsLocalTimezoneNameByCookie());
			$start_time = strtotime(date('Y-m-01', $datetime[0]));
			$end_time = strtotime(date('Y-m-01', strtotime('+1 month', $datetime[1])));
			if (!empty($datetime)) {
				$request->merge(['datetime' => [
					$start_time,
					$end_time - 1,
				]]);
			}
		}


		return $this->getList($request, "month", $export);
	}

	/**
	 * 获取数据列表
	 *
	 * @param \Illuminate\Database\Eloquent\Builder $query
	 * @param \Illuminate\Http\Request $request
	 * @param bool $export
	 * @return \Illuminate\Database\Eloquent\Collection[]|int[]|array[]
	 */
	public function getList($request, $groupBy, $export)
	{
		$query = Info::query();

		$query->where('status', Info::STATUS_NORMAL)->where('player_type', Info::PLAYER_TYPE);

		// 筛选【时间】
		$this->makeWhereParamDatetimeTimeZone($request, $query, 'end_time');

		// 筛选【平台】
		$this->makeWhereParamOrg($request, $query);
		// 筛选【平台】【排除】
		$this->makeWhereParamNot($request, 'plat_code_exclude', $query, 'plat_code');

		// 筛选【币种】
		$this->makeWhereParam($request, 'currency', $query);
		// 筛选【币种】【排除】
		$this->makeWhereParamNot($request, 'currency_exclude', $query, 'currency');

		// 统计数据
		$stats = $this->getStats(clone $query);

		// 排序
		$this->makeOrderParam($request, $query);

		//分页
		$this->makePaginationParam($request, $query);
		// 获取数据
		list($collection, $total) = $this->getData($request, $query, $groupBy);


		return [
			$collection,
			$total,
			$stats
		];
	}

	/**
	 * 获取数据和总条数
	 *
	 * @param \Illuminate\Database\Eloquent\Builder $query
	 * @return array
	 */
	private function getData($request, $query, $groupBy)
	{
		switch ($groupBy){
			case 'day':
				$columns[] ="DATE(CONVERT_TZ(DATE_FORMAT(FROM_UNIXTIME(end_time), '%Y-%m-%d %H:%i:%s'), '+08:00', '". Timed::getTimeCode() ."')) as timed";
				break;
			case 'month':
				$columns[] ="DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(end_time), '+08:00', '". Timed::getTimeCode() ."'), '%Y-%m-01') AS timed";
				break;
		}
		$columns[] = 'currency';
		$columns[] = 'plat_code';
		$columns[] = '-IFNULL(SUM(gain_player),0) as amount';
		$columns[] = 'IFNULL(SUM(bet_num),0) as bet_num';
		$columns[] = "-IFNULL(CAST(SUM(usd->'$.gain_player') AS DECIMAL({$this->decimals()},{$this->precision()})),0) AS usd_amount";
		$columns[] = "IFNULL(CAST(SUM(usd->'$.bet_num') AS DECIMAL({$this->decimals()},{$this->precision()})),0) AS usd_bet_num";

		$query->groupByRaw('timed, currency, plat_code');

		return Info::dbConnection()->transaction(function () use ($request, $query, $columns, $groupBy) {
			// 查询数据
			$collection = $query->getQuery()
				->selectRaw('SQL_CALC_FOUND_ROWS ' . implode(',', $columns))
				->get();

			// 数据条数
			$total = (int)Info::dbConnection()->select("SELECT FOUND_ROWS() as `row_count`")[0]['row_count'];

			//查询org
			$plat_codes = array_column($collection->toArray(), 'plat_code');
			$orgs = Org::whereIn('code', $plat_codes)->with([
				'config' => function($query){
					$query->select(['plat_code', 'rate']);
				}
			])->select(['code', 'name'])->get()->toArray();
			$orgArr = array_column($orgs, null, 'code');

			//转换指定时区
			date_default_timezone_set(Timed::getTimezoneName());

			$collection->transform(function ($item)use($request, $groupBy, $orgArr) {
				$rate = isset($orgArr[$item['plat_code']]) && !empty($orgArr[$item['plat_code']]['config']['rate']) ? $orgArr[$item['plat_code']]['config']['rate'] : 0;

				$item['timed'] = strtotime($item['timed']);

				return $item + ['usd'=>
					                [
						                'amount' => $item['usd_amount'],
						                'bet_num' => $item['usd_bet_num'],
						                'revenue' => $this->makeRound(($item['usd_amount']) * $rate),
					                ]
					] + [
						'revenue' => $this->makeRound(($item['amount']) * $rate),
						'rates' => [$rate],
						'rate' => $rate,
						'time_zone' => Timed::getTimezoneName(),
						'group_by' => $groupBy,
					] + ['org' => [
						'code' => $item['plat_code'],
						'name' => isset($orgArr[$item['plat_code']]) ? $orgArr[$item['plat_code']]['name'] : $item['plat_code'],
					]
					];
			});

			return [$collection, $total];
		});
	}

	/**
	 * 获取统计信息
	 *
	 * @param \Illuminate\Database\Eloquent\Builder $query
	 * @return array
	 */
	private function getStats($query)
	{
		$sts = [];
		$usd = [
			'bet_num' => 0,
			'amount' => 0,
			'revenue' => 0,
		];

		// 分平台，币种
		$collection = $query->getQuery()->selectRaw(implode(',', [
			'SUM(bet_num) as bet_num',
			'-SUM(gain_player) as amount',
			"IFNULL(CAST(SUM(usd->'$.bet_num') AS DECIMAL({$this->decimals()},{$this->precision()})),0) AS usd_bet_num",
			"-IFNULL(CAST(SUM(usd->'$.gain_player') AS DECIMAL({$this->decimals()},{$this->precision()})),0) AS usd_amount",
			'plat_code',
			'currency',
		]))
			->groupBy(['plat_code', 'currency'])
			->get();

		//查询org
		$plat_codes = array_column($collection->toArray(), 'plat_code');
		$orgs = Org\Config::whereIn('plat_code', $plat_codes)->select(['plat_code', 'rate'])->get()->toArray();
		$orgArr = array_column($orgs, 'rate', 'plat_code');

		collect($collection)->map(function ($row)use(&$usd, &$sts, $orgArr) {
			if(!isset($sts[$row['currency']])){
				$sts[$row['currency']] = [
					'bet_num' => 0,
					'amount' => 0,
					'revenue' => 0,
					'currency' => $row['currency'],
				];
			}

			$rate = isset($orgArr[$row['plat_code']]) ? $orgArr[$row['plat_code']] : 0;

			$sts[$row['currency']]['bet_num'] += $row['bet_num'];
			$sts[$row['currency']]['amount'] += $row['amount'];
			$sts[$row['currency']]['revenue'] += $this->makeRound(($row['amount']) * $rate);

			$usd['bet_num'] += $row['usd_bet_num'];
			$usd['amount'] += $row['usd_amount'];
			$usd['revenue'] += $this->makeRound(($row['usd_amount']) * $rate);
		});

		return [
			'usd' => $usd,
			'sts' => $sts,
		];
	}

    /**
     * 获取平台统计USD
     *
     * @param \Illuminate\Http\Request $request
     * @return \Illuminate\Database\Eloquent\Collection[]|array[]
     */
    public function getUsd($request)
    {
	    $query = Info::query();

	    // 筛选【平台】
	    $this->makeWhereParamOrg($request, $query);
	    // 筛选【平台】【排除】
	    $this->makeWhereParamNot($request, 'plat_code_exclude', $query, 'plat_code');
	    // 筛选【时间】
	    $this->makeWhereParamDatetimeTimeZone($request, $query, 'end_time');
	    // 筛选【币种】
	    $this->makeWhereParam($request, 'currency', $query);
	    // 筛选【币种】【排除】
	    $this->makeWhereParamNot($request, 'currency_exclude', $query, 'currency');

	    $columns[] = 'plat_code';
	    $columns[] = "CAST(SUM(usd->'$.bet_num') AS DECIMAL({$this->decimals()},{$this->precision()})) AS bet_num";
	    $columns[] = "-CAST(SUM(usd->'$.gain_player') AS DECIMAL({$this->decimals()},{$this->precision()})) AS amount";

	    // 查询数据
	    $collection = (clone $query)->getQuery()->selectRaw(implode(',', $columns))
		    ->groupBy('plat_code')
		    ->orderBy(
			    $request->input('order_column', 'amount') == 'revenue' ? 'amount' : $request->input('order_column', 'amount'),
			    $request->input('order', 'desc')
		    )
		    ->get();

	    $stats = [
		    'bet_num' => 0,
		    'amount' => 0,
		    'revenue' => 0,
	    ];

	    $collection->transform(function ($item)use(&$stats) {
		    $org = Org::where('code', $item['plat_code'])->with('config')->select('code', 'name')->first();
		    $item['org'] = [
			    'code' => $item['plat_code'],
			    'name' => !empty($org) ? $org->name : '',
		    ];
		    $rate = empty($org->config) ? 0 : $org->config->rate;
		    $item['revenue'] = $this->makeRound(($item['amount']) * $rate);
		    $item['rates'] = [$rate];

		    $stats['bet_num'] += $item['bet_num'];
		    $stats['amount'] += $item['amount'];
		    $stats['revenue'] += $item['revenue'];

		    return $item;
	    });


	    return [
		    $collection,
		    $stats
	    ];
    }
}
